<%@ Page language="vb" CodeFile="searchInvoiceCredit.aspx.vb" Inherits="searchInvoiceCredit_aspx_vb" %>

<%
    rowNumber = 1
    If (Request.QueryString("do")) = Nothing Then 
        subDo = Request.QueryString("subDo")
        dbOpen()
        RecSet = Conn.Execute("Select partnerID, partnerName, countryID From tblPartner Order by partnerName")
        If Not RecSet.EOF Then 
            Do Until RecSet.EOF
                partnerID = RecSet.Fields("partnerID").Value
                partnerName = RecSet.Fields("partnerName").Value
                countryID = RecSet.Fields("countryID").Value
                strSelectSubJS = strSelectSubJS + "new Array(" & partnerID & "," & countryID & ",""" & partnerName & """),"
                RecSet.MoveNext()
            Loop
        End IF
        RecSet.Close()
        RecSet = Nothing
        dbClose()
        dbOpen()
        RecSet = Conn.Execute("Select countryID From tblCountry Order by countryID")
        Do Until RecSet.EOF
            countryID = RecSet.Fields("countryID").Value
            strSelectSubJS = strSelectSubJS + "new Array(0," & countryID & ","" ------------------------------------  ""),"
            RecSet.MoveNext()
        Loop
        RecSet.Close()
        RecSet = Nothing
        dbClose()
        strSelectSubJS = Left(strSelectSubJS, Len(strSelectSubJS) - 1)
        strSelectSubHeadline = "Select partner"
        selectSub()
        Over()
        Response.Write("<table cellpadding='0' cellspacing='0' border='0' width='1000'>")
        Response.Write("<tr>")
        Response.Write("<td valign='top'>")
        Response.Write("<table cellpadding='2' cellspacing='2' width='450'>")
        Response.Write("<tr><td class='headline' colspan='3'>SEARCH CREDIT INVOICE<hr class='hrHeadline'></td></tr>")
        Response.Write("<form action='searchInvoiceCredit.aspx?subDo=search' method='post' name='store_productsSearch'>")
        Response.Write("<tr>")
        Response.Write("<td class='formBold'>Country:</td>")
        Response.Write("<td align='right'>")
        Response.Write("<select name='countryID' id='countryID' onchange=""set_child_listbox(this, document.store_productsSearch.partnerID,Product,'Products');"">")
        Response.Write("<option value=''> Select country  ")
        dbOpen()
        RecSet = Conn.Execute("Select countryID, country From tblCountry")
        If Not RecSet.EOF Then 
            Do Until RecSet.EOF
                countryID = RecSet.Fields("countryID").Value
                country = RecSet.Fields("country").Value
                Response.Write("<option value='" & countryID & "'> " & country & " ")
                RecSet.MoveNext()
            Loop
        End IF
        RecSet.Close()
        RecSet = Nothing
        dbClose()
        Response.Write("<option value=''> -------------------------------------- ")
        Response.Write("</select>")
        Response.Write("</td>")
        Response.Write("<td><div id='error_countryID' class='edit_error'>&laquo; OBS!</div></td>")
        Response.Write("</tr>")
        Response.Write("<tr>")
        Response.Write("<td class='formBold'>Partner:</td>")
        Response.Write("<td align='right'>")
        Response.Write("<select name='partnerID' id='partnerID' disabled>")
        Response.Write("<option value=''> - Select partner - ")
        Response.Write("<option value=''> -------------------------------------- ")
        Response.Write("</select>")
        Response.Write("</td>")
        Response.Write("<td><div id='error_partnerID' class='edit_error'>&laquo; OBS!</div></td>")
        Response.Write("</tr>")
        Response.Write("<tr><td class='formBold'>Invoice ID:</td><td align='right'><input type='text' name='invoiceCreditID' id='invoiceCreditID' size='40' maxlength='50' onkeyup='re=/(\d*)/; re.exec(this.value); this.value=RegExp.$1;'></td></tr>")
        Response.Write("<tr><td class='formBold'>References invoice ID:</td><td align='right'><input type='text' name='invoiceID' id='invoiceID' size='40' maxlength='50' onkeyup='re=/(\d*)/; re.exec(this.value); this.value=RegExp.$1;'></td></tr>")
        Response.Write("<tr><td class='formBold'>Invoice date (YYYY-MM-DD):</td><td align='right'><input type='text' name='invoiceDateFrom' id='invoiceDateFrom' size='17' maxlength='10'onkeyup='re=/([\d\-]*)/; re.exec(this.value); this.value=RegExp.$1;'> - <input type='text' name='invoiceDateTo' id='invoiceDateTo' size='17' maxlength='10'onkeyup='re=/([\d\-]*)/; re.exec(this.value); this.value=RegExp.$1;'></td></tr>")
        Response.Write("<tr><td class='formBold'>Payment date (YYYY-MM-DD):</td><td align='right'><input type='text' name='paymentDateFrom' id='paymentDateFrom' size='17' maxlength='10'onkeyup='re=/([\d\-]*)/; re.exec(this.value); this.value=RegExp.$1;'> - <input type='text' name='paymentDateTo' id='paymentDateTo' size='17' maxlength='10'onkeyup='re=/([\d\-]*)/; re.exec(this.value); this.value=RegExp.$1;'></td></tr>")
        Response.Write("<tr><td align='right' colspan='2'><input type='submit' value='Search'></td></tr>")
        Response.Write("</form>")
        Response.Write("<tr><td colspan='3'><hr class='hrHeadline'></td></tr>")
        Response.Write("</table>")
        Response.Write("</td>")
        Response.Write("<td width='100'></td>")
        Response.Write("<td valign='top'>")
        Response.Write("<table cellpadding='2' cellspacing='2' width='450'>")
        Response.Write("<tr><td class='headline' colspan='2'>STATISTIC<hr class='hrHeadline'></td></tr>")
        Response.Write("<tr><td colspan='2'><hr class='hrHeadline'></td></tr>")
        Response.Write("</table>")
        Response.Write("</td>")
        Response.Write("</tr>")
        Response.Write("<tr><td><br></td></tr>")
        Response.Write("<tr>")
        Response.Write("<td valign='top' colspan='3'>")
        Response.Write("<table cellpadding='2' cellspacing='2' width='1000'>")
        Response.Write("<tr><td class='headline' colspan='13'>SEARCH RESULT<hr class='hrHeadline'></td></tr>")
        countryID = Request.Form("countryID")
        partnerID = Request.Form("partnerID")
        invoiceCreditID = Request.Form("invoiceCreditID")
        invoiceID = Request.Form("invoiceID")
        invoiceDateFrom = Request.Form("invoiceDateFrom")
        invoiceDateTo = Request.Form("invoiceDateTo")
        paymentDateFrom = Request.Form("paymentDateFrom")
        paymentDateTO = Request.Form("paymentDateTo")
        If Len(countryID) = 0 And Len(partnerID) = 0 And Len(invoiceCreditID) = 0 And Len(invoiceDateFrom) = 0 And Len(invoiceDateTo) = 0 And Len(paymentDateFrom) = 0 And Len(paymentDateTO) = 0 Then 
            countryID = Request.QueryString("countryID")
            partnerID = Request.QueryString("partnerID")
            invoiceCreditID = Request.QueryString("invoiceCreditID")
            invoiceDateFrom = Request.QueryString("invoiceDateFrom")
            paymentDateTO = Request.QueryString("paymentDateTo")
            invoiceDateFrom = Request.QueryString("invoiceDateFrom")
            paymentDateTO = Request.QueryString("paymentDateTo")
        End IF
        If Len(countryID) > 0 Then 
            countryID = CLng(countryID)
            sqlCountryID = " And i.countryID = " & countryID & ""
        End IF
        If Len(partnerID) > 0 Then 
            partnerID = CLng(partnerID)
            sqlPartnerID = " And i.partnerID = " & partnerID & ""
        End IF
        If Len(invoiceCreditID) > 0 Then 
            invoiceCreditID = CLng(invoiceCreditID)
            sqlInvoiceCreditID = " And i.invoiceCreditID = " & invoiceCreditID & ""
        End IF
        If Len(invoiceID) > 0 Then 
            invoiceID = CLng(invoiceID)
            sqlinvoiceID = " And i.invoiceID = " & invoiceID & ""
        End IF
        If Len(invoiceDateFrom) > 0 And Len(invoiceDateTo) > 0 Then 
            sqlInvoiceDate = " And i.invoiceDate BETWEEN '" & invoiceDateFrom & "' AND '" & invoiceDateTo & "'"
        End IF
        If Len(paymentDateFrom) > 0 And Len(paymentDateTO) > 0 Then 
            sqlPaymentDate = " And i.paymentDate BETWEEN '" & paymentDateFrom & "' AND '" & paymentDateTO & "'"
        End IF
        If Len(subDo) = 0 Then 
            sqlCountryID = " And i.countryID = 1000"
        End IF
        dbOpen()
        RecSet = Conn.Execute("Select i.invoiceCreditID, i.invoiceID, i.invoiceDate, i.invoicePrinted, i.invoiceSettled, p.partnerName, c.country From ((tblInvoiceCredit i INNER JOIN tblPartner p ON p.partnerID = i.partnerID) INNER JOIN tblCountry c ON c.countryID = i.countryID) Where i.invoiceCreditID > 0" & sqlCountryID & sqlPartnerID & sqlInvoiceCreditID & sqlinvoiceID & sqlInvoiceDate & sqlPaymentDate & " Order By i.invoiceCreditID")
        If RecSet.EOF Then 
            Response.Write("<tr><td><font color='red'>No invoices found.</font></td></tr>")
        Else
            Response.Write("<tr bgcolor='#eeeeee'>")
            Response.Write("<td class='formBold'>Invoice ID</td>")
            Response.Write("<td class='formBold'>References invoice ID</td>")
            Response.Write("<td class='formBold'>Status</td>")
            Response.Write("<td class='formBold'>Partner</td>")
            Response.Write("<td class='formBold'>Country</td>")
            Response.Write("<td class='formBold'></td>")
            Response.Write("</tr>")
            Do Until RecSet.EOF
                dbinvoiceCreditID = RecSet.Fields("invoiceCreditID").Value
                dbInvoiceID = RecSet.Fields("invoiceID").Value
                dbpartnerName = RecSet.Fields("partnerName").Value
                dbCountry = RecSet.Fields("country").Value
                invoiceDate = RecSet.Fields("invoiceDate").Value
                invoicePrinted = RecSet.Fields("invoicePrinted").Value
                invoiceSettled = RecSet.Fields("invoiceSettled").Value
                If IsDBNull(invoiceDate) Then 
                    invoiceStatus = "<font color='red'>not activated</font>"
                ElseIf CInt(invoiceSettled) = 1 Then 
                    invoiceStatus = "<font color='green'>settled</font>"
                ElseIf CInt(invoicePrinted) = 0 Then 
                    invoiceStatus = "queued for printing"
                ElseIf CInt(invoicePrinted) = 1 Then 
                    invoiceStatus = "printed"
                End IF
                If rowNumber = 0 Then 
                    strGrey = " bgcolor='#eeeeee'"
                    rowNumber = 1
                Else
                    strGrey = ""
                    rowNumber = 0
                End IF
                Response.Write("<tr" & strGrey & ">")
                Response.Write("<td>" & dbinvoiceCreditID & "</td>")
                Response.Write("<td>" & dbInvoiceID & "</td>")
                Response.Write("<td>" & invoiceStatus & "</td>")
                Response.Write("<td algin='right'>" & dbpartnerName & "</td>")
                Response.Write("<td algin='right'>" & dbCountry & "</td>")
                Response.Write("<td algin='right'><a href='searchInvoiceCredit.aspx?subDo=content&countryID=" & countryID & "&partnerID=" & partnerID & "&invoiceCreditID=" & invoiceCreditID & "&invoiceDateFrom=" & invoiceDateFrom & "&invoiceDateTo=" & invoiceDateTo & "&paymentDateFrom=" & paymentDateFrom & "&paymentDateTO=" & paymentDateTO & "&dbinvoiceCreditID=" & dbinvoiceCreditID & "'>view details</a></td>")
                Response.Write("</tr>")
                RecSet.MoveNext()
            Loop
        End IF
        RecSet.Close()
        RecSet = Nothing
        dbClose()
        Response.Write("<tr><td colspan='12'><hr class='hrHeadline'></td></tr>")
        Response.Write("</table>")
        Response.Write("</td>")
        Response.Write("</tr>")
        Response.Write("<tr><td><br></td></tr>")
        If subDo = "content" Then 
            invoiceCreditID = Request.QueryString("dbinvoiceCreditID")
            invoiceCreditID = CLng(invoiceCreditID)
            Response.Write("<tr>")
            Response.Write("<td valign='top' colspan='3'>")
            Response.Write("<table cellpadding='2' cellspacing='2' width='1000'>")
            Response.Write("<tr><td class='headline' colspan='12'>LEASE INVOICE DETAILS FOR INVOICE ID " & invoiceCreditID & "<hr class='hrHeadline'></td></tr>")
            dbOpen()
            RecSet = Conn.Execute("Select i.invoiceCreditID, i.invoiceAmount, i.invoiceClaim, i.invoiceDate, i.paymentDate, i.paymentTerm, i.invoiceSettled, i.invoicePrinted, i.date, c.country, countryCurrency, p.partnerName, u.username From (((tblInvoiceCredit i INNER JOIN tblCountry c ON c.countryID = i.countryID) INNER JOIN tblPartner p ON p.partnerID = i.partnerID) LEFT JOIN tblUser u ON u.userID = i.userID) Where i.invoiceCreditID = " & invoiceCreditID & "")
            If RecSet.EOF Then 
                Response.Write("<tr><td><font color='red'>ERROR</font></td></tr>")
            Else
                invoiceCreditID = RecSet.Fields("invoiceCreditID").Value
                invoiceAmount = RecSet.Fields("invoiceAmount").Value
                invoiceClaim = RecSet.Fields("invoiceClaim").Value
                invoiceDate = RecSet.Fields("invoiceDate").Value
                paymentDate = RecSet.Fields("paymentDate").Value
                paymentTerm = RecSet.Fields("paymentTerm").Value
                invoiceSettled = RecSet.Fields("invoiceSettled").Value
                invoicePrinted = RecSet.Fields("invoicePrinted").Value
                __date = RecSet.Fields("date").Value
                country = RecSet.Fields("country").Value
                countryCurrency = RecSet.Fields("countryCurrency").Value
                partnerName = RecSet.Fields("partnerName").Value
                username = RecSet.Fields("username").Value
                If CInt(invoiceSettled) = 0 Then 
                    invoiceSettled = "<font color='red'>No</font>"
                Else
                    invoiceSettled = "<font color='green'>Yes</font>"
                End IF
                If CInt(invoicePrinted) = 0 Then 
                    invoicePrinted = "<font color='red'>No</font>"
                Else
                    invoicePrinted = "<font color='green'>Yes</font>"
                End IF
                Response.Write("<tr bgcolor='#eeeeee'>")
                Response.Write("<td class='formBold'>Invoice ID</td>")
                Response.Write("<td class='formBold'>Amount (" & countryCurrency & ")</td>")
                Response.Write("<td class='formBold'>Claim (" & countryCurrency & ")</td>")
                Response.Write("<td class='formBold'>Date of invoice</td>")
                Response.Write("<td class='formBold'>Date of payment</td>")
                Response.Write("<td class='formBold'>Payment term (days)</td>")
                Response.Write("<td class='formBold'>Settled</td>")
                Response.Write("<td class='formBold'>Printed</td>")
                Response.Write("<td class='formBold'>Referee</td>")
                Response.Write("<td class='formBold'>First created</td>")
                Response.Write("<td class='formBold'></td>")
                Response.Write("<td class='formBold'></td>")
                Response.Write("</tr>")
                Response.Write("<tr>")
                Response.Write("<td>" & invoiceCreditID & "</td>")
                Response.Write("<td>-" & invoiceAmount & "</td>")
                Response.Write("<td>-" & invoiceClaim & "</td>")
                Response.Write("<td>" & invoiceDate & "</td>")
                Response.Write("<td>" & paymentDate & "</td>")
                Response.Write("<td>" & paymentTerm & "</td>")
                Response.Write("<td>" & invoiceSettled & "</td>")
                Response.Write("<td>" & invoicePrinted & "</td>")
                Response.Write("<td>" & username & "</td>")
                Response.Write("<td>" & Left(__date, 10) & "</td>")
                If CDbl(invoiceAmount) = 0 Then 
                    Response.Write("<td></td>")
                Else
                    Response.Write("<td><a href='printInvoiceCredit.aspx?invoiceID=" & invoiceCreditID & "' target='_blank'>print</td>")
                End IF
                Response.Write("<td><a href='editInvoiceCredit.aspx?invoiceCreditID=" & invoiceCreditID & "'>view details</td>")
                Response.Write("</tr>")
            End IF
            RecSet.Close()
            RecSet = Nothing
            dbClose()
            Response.Write("<tr><td colspan='12'><hr class='hrHeadline'></td></tr>")
            Response.Write("</table>")
            Response.Write("</td>")
            Response.Write("</tr>")
            Response.Write("<tr><td><br></td></tr>")
            Response.Write("<tr>")
            Response.Write("<td valign='top' colspan='3'>")
            Response.Write("<table cellpadding='2' cellspacing='2' width='1000'>")
            Response.Write("<tr><td class='headline' colspan='10'>THIS INVOICE CONTAINS THE FOLLOWING VEHICLES<hr class='hrHeadline'></td></tr>")
            dbOpen()
            RecSet = Conn.Execute("Select ic.leaseCurrentDate, ic.invoiceID, v.vehicleID, v.regnr, v.vinNumber, v.contractNumber, v.date, p.product, pl.price, b.brand, m.model, d.dealer From ((((((tblInvoiceCreditContent ic INNER JOIN tblVehicle v ON v.vehicleID = ic.vehicleID) INNER JOIN tblProduct p ON p.productID = v.productID) INNER JOIN tblPriceList pl ON pl.priceListID = v.priceListID) INNER JOIN tblBrand b ON b.brandID = v.brandID) INNER JOIN tblModel m ON m.modelID = v.modelID) INNER JOIN tblDealer d ON d.dealerID = v.dealerID) Where ic.invoiceCreditID = " & invoiceCreditID & "")
            If RecSet.EOF Then 
                Response.Write("<tr><td><font color='red'>ERROR</font></td></tr>")
            Else
                Response.Write("<tr bgcolor='#eeeeee'>")
                Response.Write("<td class='formBold'>Car dealer</td>")
                Response.Write("<td class='formBold'>Contract number</td>")
                Response.Write("<td class='formBold'>Licence number</td>")
                Response.Write("<td class='formBold'>VIN-number</td>")
                Response.Write("<td class='formBold'>Product</td>")
                Response.Write("<td class='formBold'>Period</td>")
                Response.Write("<td class='formBold'>Invoice ID</td>")
                Response.Write("<td class='formBold'>Price (" & countryCurrency & ")</td>")
                Response.Write("<td class='formBold'>First created</td>")
                Response.Write("<td class='formBold'></td>")
                Response.Write("</tr>")
                rowNumber = 1
                Do Until RecSet.EOF
                    leaseCurrentDate = RecSet.Fields("leaseCurrentDate").Value
                    invoiceID = RecSet.Fields("invoiceID").Value
                    vehicleID = RecSet.Fields("vehicleID").Value
                    regnr = RecSet.Fields("regnr").Value
                    vinNumber = RecSet.Fields("vinNumber").Value
                    contractNumber = RecSet.Fields("contractNumber").Value
                    __date = RecSet.Fields("date").Value
                    product = RecSet.Fields("product").Value
                    price = RecSet.Fields("price").Value
                    brand = RecSet.Fields("brand").Value
                    model = RecSet.Fields("model").Value
                    dealer = RecSet.Fields("dealer").Value
                    leaseCurrentDate = Left(leaseCurrentDate, 7)
                    If rowNumber = 0 Then 
                        strGrey = " bgcolor='#eeeeee'"
                        rowNumber = 1
                    Else
                        strGrey = ""
                        rowNumber = 0
                    End IF
                    Response.Write("<tr" & strGrey & ">")
                    Response.Write("<td>" & dealer & "</td>")
                    Response.Write("<td>" & contractNumber & "</td>")
                    Response.Write("<td>" & regnr & "</td>")
                    Response.Write("<td>" & vinNumber & "</td>")
                    Response.Write("<td>" & product & "</td>")
                    Response.Write("<td>" & leaseCurrentDate & "</td>")
                    Response.Write("<td>" & invoiceID & "</td>")
                    Response.Write("<td align='right'>-" & price & "</td>")
                    Response.Write("<td>" & Left(__date, 10) & "</td>")
                    Response.Write("<td><a href='editVehicle.aspx?vehicleID=" & vehicleID & "'>view details</a></td>")
                    Response.Write("</tr>")
                    RecSet.MoveNext()
                Loop
            End IF
            RecSet.Close()
            RecSet = Nothing
            dbClose()
            Response.Write("<tr><td colspan='10'><hr class='hrHeadline'></td></tr>")
            Response.Write("</table>")
            Response.Write("</td>")
            Response.Write("</tr>")
        End IF
        Response.Write("</table>")
        Under()
    End IF
%>
